<?php
include './template.php';
include 'dbconfig/config.php';
include 'dbconfig/opendb.php';
getHeader(".");
getTopBody(".");
?>

        &nbsp;<br />
<?php

        $query = "SELECT version FROM p3dbInfo";
        $result = mysql_query($query) or die('Error, query failed'.$query);
        $row = mysql_fetch_array($result, MYSQL_ASSOC);
        $version = $row['version'];

        $query ="SELECT label,latin FROM organism ORDER BY latin;";
        $result = mysql_query($query) or die("Error, query failed: $query");
	while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
            $orgs[$row['label']] = $row['latin'];
        }

        $query ="SELECT id,link,reference FROM dataSource ORDER BY pubmed DESC;";
        $result = mysql_query($query) or die("Error, query failed: $query");
	while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
            $id = $row['id'];
            $link = $row['link'];
            if (empty($link)) {
                    $refs[$id] = $row['reference'];
            } else {
                    $refs[$id] = "<a href=\"{$link}\" target=\"_blank\">{$row['reference']}</a>";
            }
        }

        $query = "SELECT COUNT(*) as count,organism,dataSource FROM protein,proteinDataSourceRelation ".
                 "WHERE protein.id=proteinDataSourceRelation.protein ".
                 "GROUP BY organism, dataSource;";
        $result = mysql_query($query) or die("Error, query failed: $query");
	while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
            $procountorgref[$row['organism']][$row['dataSource']] = number_format($row['count']);
        }

        $query = "SELECT COUNT(*) as count,dataSource FROM protein,proteinDataSourceRelation ".
                 "WHERE protein.id=proteinDataSourceRelation.protein ".
                 "GROUP BY dataSource;";
        $result = mysql_query($query) or die("Error, query failed: $query");
	while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
            $procountref[$row['dataSource']] = number_format($row['count']);
        }

        $query = "SELECT COUNT(*) as count,organism FROM protein ".
                 "GROUP BY organism;";
        $result = mysql_query($query) or die("Error, query failed: $query");
	while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
            $procountorg[$row['organism']] = number_format($row['count']);
        }

        $query = "SELECT COUNT(*) as count FROM protein;";
        $result = mysql_query($query) or die("Error, query failed: $query");
	while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
            $procount = number_format($row['count']);
        }

        $query = "SELECT COUNT(*) as count,organism,dataSource FROM site,siteDataSourceRelation,protein ".
                 "WHERE site.id=siteDataSourceRelation.site AND site.protein=protein.id ".
                 "GROUP BY organism, dataSource;";
        $result = mysql_query($query) or die("Error, query failed: $query");
	while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
            $sitecountorgref[$row['organism']][$row['dataSource']] = number_format($row['count']);
        }

        $query = "SELECT COUNT(*) as count,dataSource FROM site,siteDataSourceRelation,protein ".
                 "WHERE site.id=siteDataSourceRelation.site AND site.protein=protein.id AND site.ptm='p' ".
                 "GROUP BY dataSource;";
        $result = mysql_query($query) or die("Error, query failed: $query");
	while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
            $sitecountref[$row['dataSource']] = number_format($row['count']);
        }

        $query = "SELECT COUNT(*) as count,organism FROM site,protein ".
                 "WHERE site.protein=protein.id AND site.ptm='p' ".
                 "GROUP BY organism;";
        $result = mysql_query($query) or die("Error, query failed: $query");
	while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
            $sitecountorg[$row['organism']] = number_format($row['count']);
        }

        $query = "SELECT COUNT(*) as count FROM site WHERE site.ptm='p';";
        $result = mysql_query($query) or die("Error, query failed: $query");
	while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
            $sitecount = number_format($row['count']);
        }
?>
        <table border="1">
        <tr><td>&nbsp;</td> <td><i>
        <?php echo implode('</i></td><td><i>',$orgs);?>
        </i></td><td>Total</td></tr>

        <?php
        foreach ($refs as $refid=>$htmlref) {
            echo "<tr><td>$htmlref</td>";
            foreach ($orgs as $orgid=>$latin) {
                echo "<td>";
                if (empty($procountorgref[$orgid][$refid]))
                    echo '0';
                else 
                    echo "<a href=\"data/protein_list.php?ref=$refid&org=$orgid\">{$procountorgref[$orgid][$refid]}</a><br/>({$sitecountorgref[$orgid][$refid]})";
                echo "</td>";
            }
            echo "<td><a href=\"data/protein_list.php?ref=$refid\">{$procountref[$refid]}</a><br/>({$sitecountref[$refid]})</td>";
            echo "</tr>";
        }

        echo "<tr><td>Total</td>";
        foreach ($orgs as $orgid=>$latin) {
            echo "<td>";
            if (empty($procountorg[$orgid]))
                echo '0';
            else
                echo "<a href=\"data/protein_list.php?org=$orgid\">{$procountorg[$orgid]}</a><br/>({$sitecountorg[$orgid]})";
            echo "</td>";
        }
        echo "<td><a href=\"data/protein_list.php\">{$procount}</a><br/>({$sitecount})</td>";
        echo "</tr>";
        ?>
        </table>
        <span class="smallText">* In each cell, the first number is the phosphoprotein count; the second number in the parentheses is the phosphosite count.</span>


<?php
	include 'dbconfig/closedb.php';
        getBottom(); ?>
